package cn.edu.hbwe.book.dao.impl;

import java.sql.Connection;




import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.ArrayListHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import cn.edu.hbwe.book.dao.BookDao;
import cn.edu.hbwe.book.entity.Book;
import cn.edu.hbwe.book.entity.Order;
import cn.edu.hbwe.book.entity.OrderItem;
import cn.edu.hbwe.book.entity.PageBean;
import cn.edu.hbwe.book.utils.DuridUtil;




public class BookDaoImpl implements BookDao{
	// 生成订单时，将商品数量减少
		public void changeProductNum(Order order) throws SQLException  {
			String sql = "update products set pnum=pnum-? where id=?";
			QueryRunner runner = new QueryRunner();
			Connection conn=DuridUtil.getConn();
			List<OrderItem> items = order.getOrderItems();
			Object[][] params = new Object[items.size()][2];
			for (int i = 0; i <items.size(); i++) {
				int count=runner.update(conn,sql,items.get(i).getBuynum(),items.get(i).getP().getId());
			}
			}
	//前台，获取本周热销商品
	public List<Object[]> getWeekHotProduct() throws SQLException {
		String sql = "SELECT products.id,products.name, "+
                             " products.imgurl,SUM(orderitem.buynum) totalsalnum "+
                     " FROM orderitem,orders,products "+
                     " WHERE orderitem.order_id = orders.id "+
                             " AND products.id = orderitem.product_id "+
                             " AND orders.paystate=1 "+
                             " AND orders.ordertime > DATE_SUB(NOW(), INTERVAL 7 DAY) "+
                     " GROUP BY products.id,products.name,products.imgurl "+
                     " ORDER BY totalsalnum DESC "+
                     " LIMIT 0,2 ";
		QueryRunner runner = new QueryRunner();
		Connection conn=DuridUtil.getConn();
		return runner.query(conn,sql, new ArrayListHandler());
	}

	// 获取当前页数据
		public List<Book> findByPage(int currentPage, int currentCount,String category) throws SQLException {
			// 要执行的sql语句
			String sql = null;
			// 参数
			Object[] obj = null;
			// 如果category不为null,代表是按分类查找
			if (!"全部商品".equals(category)) {
				sql = "select * from products  where category=? limit ?,?";
				obj = new Object[] { category, (currentPage - 1) * currentCount,
						currentCount, };
			} else {
				sql = "select * from products  limit ?,?";
				obj = new Object[] { (currentPage - 1) * currentCount,
						currentCount, };
			}
			QueryRunner runner = new QueryRunner();
			Connection conn=DuridUtil.getConn();
			return runner.query(conn,sql, new BeanListHandler<Book>(Book.class),
					obj);
		}
	// 获取数据总条数
		public int findAllCount(String category) throws SQLException {
			String sql = "select count(*) from products";

			QueryRunner runner=new QueryRunner();
			Connection conn=DuridUtil.getConn();
			if (!"全部商品".equals(category)) {
				sql += " where category=?";

				Long count = (Long) runner.query(conn,sql, new ScalarHandler(), category);
				return count.intValue();
			} else {
				Long count = (Long) runner.query(conn,sql, new ScalarHandler());

				return count.intValue();
			}
		}
	public List<Book> findAll() {
		List<Book> books= new ArrayList<Book>();
		ResultSetHandler<List<Book>> h=new BeanListHandler<Book>(Book.class);
		QueryRunner qr=new QueryRunner();
				try {			
					Connection conn=DuridUtil.getConn();
					String sql="select * from products";
					books=qr.query(conn, sql, h);
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				
			
			return books;
			
		}

	public void deleteBook(String id) {
		// TODO Auto-generated method stub
		QueryRunner qr=new QueryRunner();
		try {			
			Connection conn=DuridUtil.getConn();
			String sql="delete from products where id=?";
			int i=qr.update(conn, sql, id);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	
	}

	public List<Book> findBooksbyCondition(String id,String name,String category,String minprice,String maxprice) {
		// TODO Auto-generated method stub
		List<Book> books= new ArrayList<Book>();
		ResultSetHandler<List<Book>> h=new BeanListHandler<Book>(Book.class);
			List<Object> list = new ArrayList<Object>();
			Connection conn=DuridUtil.getConn();
			String sql = "select * from products where 1=1 ";
		
			QueryRunner runner=new QueryRunner();

			if (id != null && id.trim().length() > 0) {
				sql += " and id like ?";
				list.add(id);
			}

			if (name != null && name.trim().length() > 0) {
				sql += " and name like ?";
				list.add(name);
			}
			if (category != null && category.trim().length() > 0) {
				sql += " and category=?";
				list.add(category);
			}
			if (minprice != null && maxprice != null
					&& minprice.trim().length() > 0 && maxprice.trim().length() > 0) {
				sql += " and price between ? and ?";
				list.add(minprice);
				list.add(maxprice);
			}
			try {
				Object[] params = list.toArray();
				books=runner.query(conn, sql, h,params);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			return books;
			
		
	}	
	public void addbookList(Book book) {
		QueryRunner qr=new QueryRunner();
		try {
			String sql = "insert into products(id,name,price,category,pnum,imgurl,description) values(?,?,?,?,?,?,?)";
			Connection conn=DuridUtil.getConn();
			qr.update(conn, sql,book.getId(),book.getName(),book.getPrice(),book.getCategory(),book.getPnum(),book.getImgurl(),book.getDescription());//数据自己补
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	
	}

	public void updateBookMessage(String id,Book book) {
		// TODO Auto-generated method stub
		QueryRunner qr=new QueryRunner();
		try {
			String sql="update products set name=?,price=?,category=?,pnum=?,imgurl=?,description=? where id=?";
			Connection conn=DuridUtil.getConn();
			qr.update(conn, sql,book.getName(),book.getPrice(),book.getCategory(),book.getPnum(),book.getImgurl(),book.getDescription(),id);//数据自己补
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}	
	
	public  Book Findone(String id){
		
		Book book=null;
		ResultSetHandler<Book> h=new BeanHandler<Book>(Book.class);
		QueryRunner qr=new QueryRunner();
			try {
				String sql="select * from products where id=?";
				Connection conn=DuridUtil.getConn();
				book=qr.query(conn,sql, h,id);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
		
		
		return book;
	}	
	// 销售榜单
		public List<Object[]> salesList(String year, String month) throws SQLException {
			QueryRunner qr=new QueryRunner();
			String sql = "SELECT products.name,SUM(orderitem.buynum) totalsalnum FROM orders,products,orderItem WHERE orders.id=orderItem.order_id AND products.id=orderItem.product_id AND orders.paystate=1 and year(ordertime)=? and month(ordertime)=? GROUP BY products.name ORDER BY totalsalnum DESC";
			Connection conn=DuridUtil.getConn();
			return qr.query(conn,sql, new ArrayListHandler(), year, month);
		}
		
		public void updateProductNum(List<OrderItem> items) throws SQLException {
			
			String sql = "update products set pnum=pnum+? where id=?";
			
			try {		
				Object[][] params = new Object[items.size()][2];
				QueryRunner qr=new QueryRunner();
				Connection conn=DuridUtil.getConn();
				for (int i = 0; i < params.length; i++) {
					params[i][0] = items.get(i).getBuynum();
					params[i][1] = items.get(i).getP().getId();
				}
				qr.update(conn,sql, params);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
			}
		public Book findProductById(String id) throws SQLException {
			String sql = "select * from products where id=?";
			QueryRunner qr=new QueryRunner();
			Connection conn=DuridUtil.getConn();
			return qr.query(conn,sql, new BeanHandler<Book>(Book.class), id);
		}
	
		
}


